﻿using System;
using System.Collections;
using System.Text;

namespace Dotnet.Utils.Utility.Sql
{
    public class SQLUtil
    {
    private static readonly string[] InjectionKeyWords = {
                                                                "select",
                                                                "insert",
                                                                "delete",
                                                                "count(",
                                                                "drop table",
                                                                "update",
                                                                "truncate",
                                                                "asc(",
                                                                "mid(",
                                                                "char(",
                                                                "xp_cmdshell",
                                                                "administrators",
                                                                "exec master",
                                                                "net user",
                                                                ";",
                                                                "--",
                                                            };

        /// <summary>
        /// 处理存在注入漏洞的字符串
        /// </summary>
        /// <param name="orig"></param>
        /// <returns></returns>

        public static string ReplaceInjectionChar(string orig)
        {
            if (string.IsNullOrWhiteSpace(orig))
            {
                return orig;
            }

            CheckInjectionKeyWord(orig);

            return orig.Replace("'", "''");
        }

        /// <summary>
        /// 检查存在安全漏洞的字符
        /// </summary>
        /// <param name="word"></param>
        public static void CheckInjectionKeyWord(string word)
        {
            var allText = word.Replace("\r", " ").Replace("\n", " ").ToLower();
            while (allText.Contains("  "))
            {
                allText = allText.Replace("  ", " ");
            }

            foreach (string i in InjectionKeyWords)
            {
                if (word.Contains(i))
                {
                    throw new Exception("无效参数：" + word);
                }
            }
        }

        /// <summary>
        /// 返回数据列表的表示的SQL语句片断
        /// </summary>
        /// <param name="aryObj"><see cref="System.Collections.ArrayList"/>，数据列表</param>
        /// <example>
        /// <code>
        /// ArrayList aryId = new ArrayList();
        /// aryDept.Add(1);
        /// aryDept.Add(2);
        /// aryDept.Add(3);
        /// string sId = DBUtil.ArrayToSQLIn(aryId);
        /// string sSql = "Select t.* From TABLE_NAME t Where t.ID In (" + sId + ")";
        /// </code>
        /// 结果sSql："Select t.* From TABLE_NAME t Where t.ID In (1, 2, 3)"
        /// </example>
        /// <returns><see cref="string"/>，返回SQL语句片断</returns>
        [Obsolete("使用Array参数的方法")]
        public static string ArrayToSQLIn(ArrayList aryObj)
        {
            if (aryObj.Count == 0)
                return "";
            StringBuilder strBuf = new StringBuilder();
            foreach (object obj in aryObj)
            {
                strBuf.Append(",");
                if (obj is string)
                {
                    strBuf.Append("'").Append(ReplaceInjectionChar(obj.ToString())).Append("'");
                }
                else
                {
                    strBuf.Append(obj.ToString());
                }
            }
            return strBuf.ToString(1, strBuf.Length - 1);
        }

        /// <summary>
        /// 返回字符串数组表示的SQL语句片断
        /// </summary>
        /// <param name="aryObj"><see cref="string"/>[]，字符串数组</param>
        /// <returns><see cref="string"/>，返回SQL语句片断</returns>
        public static string ArrayToSQLIn(string[] aryObj)
        {
            return ArrayToSQLIn(aryObj, false);
        }

        /// <summary>
        /// 返回字符串数组表示的SQL语句片断
        /// </summary>
        /// <param name="aryObj"><see cref="string"/>[]，整数数组</param>
        /// <returns><see cref="string"/>，返回SQL语句片断</returns>
        public static string ArrayToSQLIn(int[] aryObj)
        {
            if (aryObj.Length == 0)
                return "";
            StringBuilder strBuf = new StringBuilder();
            foreach (int obj in aryObj)
            {
                strBuf.Append(",");
                strBuf.Append(obj);
            }
            return strBuf.ToString(1, strBuf.Length - 1);
        }/// <summary>

         /// 返回字符串数组表示的SQL语句片断
         /// </summary>
         /// <param name="aryObj"><see cref="string"/>[]，字符串数组</param>
         /// <param name="bAsNum"><see cref="bool"/>，是否当成数字格式处理，如果是 不加引号</param>
         /// <returns><see cref="string"/>，返回SQL语句片断</returns>
        public static string ArrayToSQLIn(string[] aryObj, bool bAsNum)
        {
            if (aryObj.Length == 0)
                return "";
            StringBuilder strBuf = new StringBuilder();
            foreach (string obj in aryObj)
            {
                strBuf.Append(",");
                if (!bAsNum)
                    strBuf.Append("'");
                strBuf.Append(ReplaceInjectionChar(obj));
                if (!bAsNum)
                    strBuf.Append("'");
            }
            return strBuf.ToString(1, strBuf.Length - 1);
        }
    }
}